package javaapplication1.dao.oracleImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import javaapplication1.model.Pet;
import javaapplication1.dao.PetDao;

public class PetDaoOracleImpl implements PetDao {

	private Connection connection = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;

	@Override
	public boolean createPet(Pet pet) {
		boolean flag = false;
		try {
			String sql = "insert into T_PET values(SEQ_T_PET.nextval,?,?,?,?,?,?,?,?)";
			connection = OracleConnection.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setString(1, pet.getName());
			ps.setString(2, pet.getTypeName());
			ps.setInt(3, pet.getHealth());
			ps.setInt(4, pet.getLove());
			ps.setDate(5, new java.sql.Date(pet.getBirthday().getTime()));
			ps.setInt(6, pet.getOwnerId());
			ps.setInt(7, pet.getStoreId());
			ps.setInt(8, pet.getPrice());
			int line = ps.executeUpdate();
			flag = (line == 1) ? true : false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnection.closeConn(connection, ps, rs);
		}
		return flag;
	}

	@Override
	public boolean deletePet(int id) {
		boolean flag = false;
		try {
			String sql = "delete from T_PET where id = ?";
			connection = OracleConnection.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			int line = ps.executeUpdate();
			flag = (line == 1) ? true : false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnection.closeConn(connection, ps, rs);
		}
		return flag;
	}

	@Override
	public boolean updatePet(Pet pet) {
		return false;
	}

	@Override
	public boolean updatePet(int id, int ownerId, int storeId) {
		boolean flag = false;
		try {
			String sql = "update T_PET set OWNER_ID = ?,STORE_ID = ? where id = ?";
			connection = OracleConnection.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, ownerId);
			ps.setInt(2, storeId);
			ps.setInt(3, id);
			int line = ps.executeUpdate();
			flag = (line == 1) ? true : false;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnection.closeConn(connection, ps, rs);
		}
		return flag;
	}

	@Override
	public Pet retrievePet(int id) {
		Pet pet = null;
		try {
			String sql = "select * from T_PET where id = ?";
			connection = OracleConnection.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if (rs.next()) {
				pet = new Pet();
				pet.setId(rs.getInt("ID"));
				pet.setName(rs.getString("NAME"));
				pet.setTypeName(rs.getString("TYPENAME"));
				pet.setHealth(rs.getInt("HEALTH"));
				pet.setLove(rs.getInt("LOVE"));
				pet.setBirthday(rs.getDate("BIRTHDAY"));
				pet.setOwnerId(rs.getInt("OWNER_ID"));
				pet.setStoreId(rs.getInt("STORE_ID"));
				pet.setPrice(rs.getInt("PRICE"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnection.closeConn(connection, ps, rs);
		}
		return pet;
	}

	@Override
	public List<Pet> retrievePetAll() {
		List<Pet> petList = new ArrayList<Pet>();
		try {
			String sql = "select * from T_PET";
			connection = OracleConnection.getConnection();
			ps = connection.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				Pet pet = new Pet();
				pet.setId(rs.getInt("ID"));
				pet.setName(rs.getString("NAME"));
				pet.setTypeName(rs.getString("TYPENAME"));
				pet.setHealth(rs.getInt("HEALTH"));
				pet.setLove(rs.getInt("LOVE"));
				pet.setBirthday(rs.getDate("BIRTHDAY"));
				pet.setOwnerId(rs.getInt("OWNER_ID"));
				pet.setStoreId(rs.getInt("STORE_ID"));
				pet.setPrice(rs.getInt("PRICE"));
				petList.add(pet);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnection.closeConn(connection, ps, rs);
		}
		return petList;
	}

	@Override
	public List<Pet> retrievePetByOwnerId(int ownerId) {
		List<Pet> petList = new ArrayList<Pet>();
		try {
			String sql = "select * from T_PET where OWNER_ID = ?";
			connection = OracleConnection.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, ownerId);
			rs = ps.executeQuery();
			while (rs.next()) {
				Pet pet = new Pet();
				pet.setId(rs.getInt("ID"));
				pet.setName(rs.getString("NAME"));
				pet.setTypeName(rs.getString("TYPENAME"));
				pet.setHealth(rs.getInt("HEALTH"));
				pet.setLove(rs.getInt("LOVE"));
				pet.setBirthday(rs.getDate("BIRTHDAY"));
				pet.setOwnerId(rs.getInt("OWNER_ID"));
				pet.setStoreId(rs.getInt("STORE_ID"));
				pet.setPrice(rs.getInt("PRICE"));
				petList.add(pet);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnection.closeConn(connection, ps, rs);
		}
		return petList;
	}

	@Override
	public List<Pet> retrievePetByStoreId(int storeId) {
		List<Pet> petList = new ArrayList<Pet>();
		try {
			String sql = "select * from T_PET where STORE_ID = ?";
			connection = OracleConnection.getConnection();
			ps = connection.prepareStatement(sql);
			ps.setInt(1, storeId);
			rs = ps.executeQuery();
			while (rs.next()) {
				Pet pet = new Pet();
				pet.setId(rs.getInt("ID"));
				pet.setName(rs.getString("NAME"));
				pet.setTypeName(rs.getString("TYPENAME"));
				pet.setHealth(rs.getInt("HEALTH"));
				pet.setLove(rs.getInt("LOVE"));
				pet.setBirthday(rs.getDate("BIRTHDAY"));
				pet.setOwnerId(rs.getInt("OWNER_ID"));
				pet.setStoreId(rs.getInt("STORE_ID"));
				pet.setPrice(rs.getInt("PRICE"));
				petList.add(pet);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			OracleConnection.closeConn(connection, ps, rs);
		}
		return petList;
	}

}